import pandas as pd
import numpy as np
from numpy import array
import cvxpy as cp

#读数据库煤种煤种配置表中数据
#'select * from 模型煤种配置表 where 模板号 = ’P_模板号‘'
max_constant = 100000
min_constant = 0
data = pd.read_excel('煤炭列表.xlsx')
tmpl_no = 'TM202308181451'
data_meizhong = pd.read_excel('模型煤种配置表.xlsx')
data_meizhong = data_meizhong[data_meizhong['版本号'] == tmpl_no]

data_qianti = pd.read_excel('前提条件配置表.xlsx')
data_zhuyao = pd.read_excel('主要参数配置表.xlsx')
data_bilikegong = pd.read_excel('煤比例可供资源配置表.xlsx')
data_meizhi = pd.read_excel('煤质信息配置表.xlsx')
data_shangxiaxian = pd.read_excel('煤质上下限配置表.xlsx')
data_price = pd.read_excel('手工价格配置表.xlsx')

data_meizhong.rename(columns={'品种': 'BIG_VAR'}, inplace=True)
data_meizhong.rename(columns={'性状': 'VAR'}, inplace=True)
data_meizhong.rename(columns={'品名': 'PROD_DSCR'}, inplace=True)
#数据处理，不要外购焦粉
data_meizhong = data_meizhong[(data_meizhong['BIG_VAR']!='烧结煤')|(data_meizhong['VAR']!='焦粉')]
data_meizhong = data_meizhong.reset_index(drop=True)
data0 = data_meizhong.copy()
data0.drop(['主键'], axis=1, inplace=True)
data0.drop(['版本号'], axis=1, inplace=True)
data0.drop(['创建时间'], axis=1, inplace=True)
data0.drop(['创建人'], axis=1, inplace=True)
data0.drop(['修改时间'], axis=1, inplace=True)
data0.drop(['修改人'], axis=1, inplace=True)
row_count = data0.shape[0]
print("行数：", row_count)
def __cal_rank_pinzhong(x):
    rst = 0
    if x.BIG_VAR == '喷吹煤':
        rst = 1
    elif x.BIG_VAR == '发电煤':
        rst = 2
    elif x.BIG_VAR == '烧结煤':
        rst = 3
    return rst
data0['pinzhong'] = data0.apply(lambda x: __cal_rank_pinzhong(x), axis=1)
def __cal_rank_xingzhuang(x):
    rst = 0
    if x.BIG_VAR == '喷吹煤':
        if x.VAR == '烟煤':
            rst = 1
        elif x.VAR == '无烟煤':
            rst = 2
        elif x.VAR == '兰炭':
            rst = 3
    elif x.BIG_VAR == '发电煤':
        if x.VAR == '大同类':
            rst = 1
        elif x.VAR == '神府类':
            rst = 2
        elif x.VAR == '兰炭类':
            rst = 3
    elif x.BIG_VAR == '烧结煤':
        if x.VAR == '无烟煤':
            rst = 1
        elif x.VAR == '兰炭':
            rst = 2
    return rst
data0['xingzhuang'] = data0.apply(lambda x: __cal_rank_xingzhuang(x), axis=1)
def __cal_rank_group(x):
    rst = str(x.pinzhong)+'_'+str(x.xingzhuang)
    return rst
data0['group'] = data0.apply(lambda x: __cal_rank_group(x), axis=1)
data0 = data0.reset_index(drop=False)
data0.rename(columns={'index': 'index_old'}, inplace=True)

data0['rank0'] = data0['index_old'].groupby(data0['group']).rank()
data0['pinming'] = data0['rank0'].astype(int)
def __cal_rank_mark(x):
    rst = str(x.group)+'_'+str(x.pinming)
    return rst
data0['mark'] = data0.apply(lambda x: __cal_rank_mark(x), axis=1)
# data0.drop(['group'], axis=1, inplace=True)
data0.drop(['rank0'], axis=1, inplace=True)
#生成mark列拼接动态参数
#统计个数，做个var_df以便后续对应
big_var_list = ['喷吹煤','发电煤','烧结煤']
var_list1 = ['烟煤','无烟煤','兰炭']
var_list2 = ['大同类','神府类','兰炭类']
var_list3 = ['无烟煤','兰炭']
var_df = pd.DataFrame(columns=['BIG_VAR', 'VAR', 'group', 'count'])
dict = {}
var_list = []
for i in range(1,len(big_var_list)+1):
    print(i)
    exec('var_list =var_list{}'.format(i))
    print(var_list)
    for j in range(1, len(var_list) + 1):
        print(j)
        df = data[(data['BIG_VAR'] == big_var_list[i - 1]) & (data['VAR'] == var_list[j - 1])]
        row_count = df.shape[0]
        print("行数：", row_count)
        dict['BIG_VAR'] = big_var_list[i - 1]
        dict['VAR'] = var_list[j - 1]
        dict['group'] = str(i)+'_'+str(j)
        dict['count'] = row_count
        new_row = pd.Series(dict)
        var_df = var_df.append(new_row, ignore_index=True)
print('finish')
# #动态变量测试
###价格
data_price.rename(columns={'品名': 'PROD_DSCR'}, inplace=True)
data_price.rename(columns={'价格': 'PRICE'}, inplace=True)
data_price = data_price[['PROD_DSCR','PRICE']]
v = ['PROD_DSCR']
df3 = pd.merge(data0, data_price, on=v, how='left')
df3.PRICE.fillna(max_constant,inplace = True)
for index, row in df3.iterrows():
    # print(index)
    # print(row['mark'])
    exec('price_{} ={}'.format(row['mark'],row['PRICE']))
    exec('print(price_{})'.format(row['mark']))
###可控资源
data_bilikegong.rename(columns={'品名': 'PROD_DSCR'}, inplace=True)
data_bilikegong.rename(columns={'可供资源': 'WT'}, inplace=True)
data_bilikegong.rename(columns={'性状': 'VAR'}, inplace=True)
data_bilikegong_1 = data_bilikegong[data_bilikegong['性状or品名']=='品名']
data_bilikegong_1 = data_bilikegong_1.reset_index(drop=True)
data_bilikegong_1 = data_bilikegong_1[['PROD_DSCR','WT']]
v = ['PROD_DSCR']
df3 = pd.merge(data0, data_bilikegong_1, on=v, how='left')
#可供资源为空，无穷大库存
df3.WT.fillna(max_constant,inplace = True)
for index, row in df3.iterrows():
    exec('kegongziyuan_{} ={}'.format(row['mark'],row['WT']))
###比例上下限
data_bilikegong.rename(columns={'比例上限': 'UL'}, inplace=True)
data_bilikegong.rename(columns={'比例下限': 'LL'}, inplace=True)
data_bilikegong_1 = data_bilikegong[data_bilikegong['性状or品名']=='品名']
data_bilikegong_1 = data_bilikegong_1.reset_index(drop=True)
data_bilikegong_1 = data_bilikegong_1[['PROD_DSCR','UL','LL']]
v = ['PROD_DSCR']
df3 = pd.merge(data0, data_bilikegong_1, on=v, how='left')
#比例0-100
df3.UL.fillna(100,inplace = True)
df3.LL.fillna(0,inplace = True)

for index, row in df3.iterrows():
    exec('ul_{} ={}'.format(row['mark'],row['UL']))
    exec('ll_{} ={}'.format(row['mark'],row['LL']))
data_bilikegong_1 = data_bilikegong[data_bilikegong['性状or品名']=='性状']
data_bilikegong_1 = data_bilikegong_1.reset_index(drop=True)
data_bilikegong_1 = data_bilikegong_1[['VAR','UL','LL']]
v = ['VAR']
df3 = pd.merge(var_df, data_bilikegong_1, on=v, how='left')
#比例0-100
df3.UL.fillna(100,inplace = True)
df3.LL.fillna(0,inplace = True)
for index, row in df3.iterrows():
    exec('ul_{} ={}'.format(row['group'],row['UL']))
    exec('ll_{} ={}'.format(row['group'],row['LL']))
###煤质
data_meizhi.rename(columns={'品名': 'PROD_DSCR'}, inplace=True)
data_meizhi.rename(columns={'水分': 'shui'}, inplace=True)
data_meizhi.rename(columns={'灰分': 'hui'}, inplace=True)
data_meizhi.rename(columns={'挥发分': 'huifa'}, inplace=True)
data_meizhi.rename(columns={'硫分': 'liu'}, inplace=True)
data_meizhi.rename(columns={'固定碳': 'gu'}, inplace=True)
data_meizhi.rename(columns={'可磨性': 'mo'}, inplace=True)
data_meizhi.rename(columns={'热值': 're'}, inplace=True)
data_meizhi.rename(columns={'C': 'cc'}, inplace=True)

data_meizhi_1 = data_meizhi[['PROD_DSCR','shui','hui','huifa','liu','gu','mo','re','cc']]
v = ['PROD_DSCR']
df3 = pd.merge(data0, data_meizhi_1, on=v, how='left')
df3.fillna(max_constant,inplace = True)
for index, row in df3.iterrows():
    exec('shui_{} ={}'.format(row['mark'],row['shui']))
    exec('hui_{} ={}'.format(row['mark'],row['hui']))
    exec('huifa_{} ={}'.format(row['mark'],row['huifa']))
    exec('liu_{} ={}'.format(row['mark'],row['liu']))
    exec('gu_{} ={}'.format(row['mark'],row['gu']))
    exec('mo_{} ={}'.format(row['mark'],row['mo']))
    exec('re_{} ={}'.format(row['mark'],row['re']))
    exec('cc_{} ={}'.format(row['mark'],row['cc']))
###煤质上下限
data_shangxiaxian_1 = data_shangxiaxian[(data_shangxiaxian['上限or下限']=='上限')&(data_shangxiaxian['品种']=='喷吹煤')]
data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
data_shangxiaxian_1.fillna(max_constant,inplace = True)
penchui_shui_ul = data_shangxiaxian_1.loc[0]['水分']
penchui_hui_ul = data_shangxiaxian_1.loc[0]['灰分']
penchui_huifa_ul = data_shangxiaxian_1.loc[0]['挥发分']
penchui_liu_ul = data_shangxiaxian_1.loc[0]['硫分']
penchui_gu_ul = data_shangxiaxian_1.loc[0]['固定碳']
penchui_mo_ul = data_shangxiaxian_1.loc[0]['可磨性']
penchui_re_ul = data_shangxiaxian_1.loc[0]['热值']
penchui_cc_ul = data_shangxiaxian_1.loc[0]['C']
data_shangxiaxian_1 = data_shangxiaxian[(data_shangxiaxian['上限or下限']=='下限')&(data_shangxiaxian['品种']=='喷吹煤')]
data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
data_shangxiaxian_1.fillna(min_constant,inplace = True)

penchui_shui_ll = data_shangxiaxian_1.loc[0]['水分']
penchui_hui_ll = data_shangxiaxian_1.loc[0]['灰分']
penchui_huifa_ll = data_shangxiaxian_1.loc[0]['挥发分']
penchui_liu_ll = data_shangxiaxian_1.loc[0]['硫分']
penchui_gu_ll = data_shangxiaxian_1.loc[0]['固定碳']
penchui_mo_ll = data_shangxiaxian_1.loc[0]['可磨性']
penchui_re_ll = data_shangxiaxian_1.loc[0]['热值']
penchui_cc_ll = data_shangxiaxian_1.loc[0]['C']
data_shangxiaxian_1 = data_shangxiaxian[(data_shangxiaxian['上限or下限']=='上限')&(data_shangxiaxian['品种']=='发电煤')]
data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
data_shangxiaxian_1.fillna(max_constant,inplace = True)

fadian_shui_ul = data_shangxiaxian_1.loc[0]['水分']
fadian_hui_ul = data_shangxiaxian_1.loc[0]['灰分']
fadian_huifa_ul = data_shangxiaxian_1.loc[0]['挥发分']
fadian_liu_ul = data_shangxiaxian_1.loc[0]['硫分']
fadian_gu_ul = data_shangxiaxian_1.loc[0]['固定碳']
fadian_mo_ul = data_shangxiaxian_1.loc[0]['可磨性']
fadian_re_ul = data_shangxiaxian_1.loc[0]['热值']
fadian_cc_ul = data_shangxiaxian_1.loc[0]['C']
data_shangxiaxian_1 = data_shangxiaxian[(data_shangxiaxian['上限or下限']=='下限')&(data_shangxiaxian['品种']=='发电煤')]
data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
data_shangxiaxian_1.fillna(min_constant,inplace = True)

fadian_shui_ll = data_shangxiaxian_1.loc[0]['水分']
fadian_hui_ll = data_shangxiaxian_1.loc[0]['灰分']
fadian_huifa_ll = data_shangxiaxian_1.loc[0]['挥发分']
fadian_liu_ll = data_shangxiaxian_1.loc[0]['硫分']
fadian_gu_ll = data_shangxiaxian_1.loc[0]['固定碳']
fadian_mo_ll = data_shangxiaxian_1.loc[0]['可磨性']
fadian_re_ll = data_shangxiaxian_1.loc[0]['热值']
fadian_cc_ll = data_shangxiaxian_1.loc[0]['C']
data_shangxiaxian_1 = data_shangxiaxian[(data_shangxiaxian['上限or下限']=='上限')&(data_shangxiaxian['品种']=='烧结煤')]
data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
data_shangxiaxian_1.fillna(max_constant,inplace = True)

shaojie_shui_ul = data_shangxiaxian_1.loc[0]['水分']
shaojie_hui_ul = data_shangxiaxian_1.loc[0]['灰分']
shaojie_huifa_ul = data_shangxiaxian_1.loc[0]['挥发分']
shaojie_liu_ul = data_shangxiaxian_1.loc[0]['硫分']
shaojie_gu_ul = data_shangxiaxian_1.loc[0]['固定碳']
shaojie_mo_ul = data_shangxiaxian_1.loc[0]['可磨性']
shaojie_re_ul = data_shangxiaxian_1.loc[0]['热值']
shaojie_cc_ul = data_shangxiaxian_1.loc[0]['C']
data_shangxiaxian_1 = data_shangxiaxian[(data_shangxiaxian['上限or下限']=='下限')&(data_shangxiaxian['品种']=='烧结煤')]
data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
data_shangxiaxian_1.fillna(min_constant,inplace = True)

shaojie_shui_ll = data_shangxiaxian_1.loc[0]['水分']
shaojie_hui_ll = data_shangxiaxian_1.loc[0]['灰分']
shaojie_huifa_ll = data_shangxiaxian_1.loc[0]['挥发分']
shaojie_liu_ll = data_shangxiaxian_1.loc[0]['硫分']
shaojie_gu_ll = data_shangxiaxian_1.loc[0]['固定碳']
shaojie_mo_ll = data_shangxiaxian_1.loc[0]['可磨性']
shaojie_re_ll = data_shangxiaxian_1.loc[0]['热值']
shaojie_cc_ll = data_shangxiaxian_1.loc[0]['C']

#年度前提条件表
kongmeizhibiao = data_qianti.loc[0]['控煤指标']
tieshuichanliang = data_qianti.loc[0]['铁水产量']
jiaotanchanliang = data_qianti.loc[0]['焦炭产量']
fadianliang = data_qianti.loc[0]['发电量']
shaojiechanliang = data_qianti.loc[0]['烧结产量']
zongmeibi = data_qianti.loc[0]['总煤比']
#纯煤比 = 总煤比 - CDQ比
chunmeibi = data_qianti.loc[0]['纯煤比']
zongjiaobi = data_qianti.loc[0]['总焦比']
shaojieranliaobi = data_qianti.loc[0]['烧结燃料比']
#主要参数表
chengjiaolv = data_zhuyao.loc[0]['成焦率']
yejinjiaolv = data_zhuyao.loc[0]['冶金焦率']
cujiaolv = data_zhuyao.loc[0]['粗焦率']
meidianbi = data_zhuyao.loc[0]['煤电比']
fadianmeihao = data_zhuyao.loc[0]['发电煤耗']
lianjiaomeishuifen = data_zhuyao.loc[0]['炼焦煤水分']
penchuimeishuifen = data_zhuyao.loc[0]['喷吹煤水分']
shaojiemeishuifen = data_zhuyao.loc[0]['烧结煤水分']
CDQbi = data_zhuyao.loc[0]['CDQ比']
meitanwushunlv = data_zhuyao.loc[0]['煤炭亏损率']
waigoujiaoshuifen = data_zhuyao.loc[0]['外购焦水分']
waigoujiaofenlv = data_zhuyao.loc[0]['外购焦焦粉率']
shaojiejiaofenshuifen = data_zhuyao.loc[0]['烧结焦水分']
lantanshuifen = data_zhuyao.loc[0]['兰炭水分']
jiaotanchaochanlv = data_zhuyao.loc[0]['炼焦超产率']
#公式计算
chunmeibi = zongmeibi - CDQbi
print('参数读取完毕！！')
############################
#参数读取完毕
# 炼焦煤
CAL_WT_lianjiaomei = jiaotanchanliang / chengjiaolv * 100 / (100 - lianjiaomeishuifen) * 100 * (100 + meitanwushunlv) / 100
WT_lianjiaomei = CAL_WT_lianjiaomei * (100 + jiaotanchaochanlv) / 100
# 外购焦炭    （补炼焦煤）
WT_waigoujiaotan = (tieshuichanliang * zongjiaobi / 1000 - jiaotanchanliang * yejinjiaolv / 100) / (100 - waigoujiaofenlv) * 100 / (100 - waigoujiaoshuifen) * 100

#构建配置系数df
coef_df = pd.DataFrame(columns=['mark'])
dict = {}
data0_1 = data0[(data0['BIG_VAR']=='喷吹煤')&(data0['VAR']=='烟煤')]
data0_1 = data0_1.reset_index(drop=True)
for index, row in data0_1.iterrows():
    exec("dict['mark'] = '{}'".format(row['mark']))
    new_row = pd.Series(dict)
    coef_df = coef_df.append(new_row, ignore_index=True)
data0_1 = data0[(data0['BIG_VAR']=='喷吹煤')&(data0['VAR']=='无烟煤')]

data0_1 = data0_1.reset_index(drop=True)
for index, row in data0_1.iterrows():
    exec("dict['mark'] = '{}'".format(row['mark']))
    new_row = pd.Series(dict)
    coef_df = coef_df.append(new_row, ignore_index=True)
data0_1 = data0[(data0['BIG_VAR']=='发电煤')&(data0['VAR']=='大同类')]

data0_1 = data0_1.reset_index(drop=True)
for index, row in data0_1.iterrows():
    exec("dict['mark'] = '{}'".format(row['mark']))
    new_row = pd.Series(dict)
    coef_df = coef_df.append(new_row, ignore_index=True)
data0_1 = data0[(data0['BIG_VAR']=='发电煤')&(data0['VAR']=='神府类')]

data0_1 = data0_1.reset_index(drop=True)
for index, row in data0_1.iterrows():
    exec("dict['mark'] = '{}'".format(row['mark']))
    new_row = pd.Series(dict)
    coef_df = coef_df.append(new_row, ignore_index=True)
data0_1 = data0[(data0['BIG_VAR']=='烧结煤')&(data0['VAR']=='无烟煤')]

data0_1 = data0_1.reset_index(drop=True)
for index, row in data0_1.iterrows():
    exec("dict['mark'] = '{}'".format(row['mark']))
    new_row = pd.Series(dict)
    coef_df = coef_df.append(new_row, ignore_index=True)
data0_1 = data0[(data0['BIG_VAR']=='喷吹煤')&(data0['VAR']=='兰炭')]

data0_1 = data0_1.reset_index(drop=True)
for index, row in data0_1.iterrows():
    exec("dict['mark'] = '{}'".format(row['mark']))
    new_row = pd.Series(dict)
    coef_df = coef_df.append(new_row, ignore_index=True)
data0_1 = data0[(data0['BIG_VAR']=='发电煤')&(data0['VAR']=='兰炭类')]

data0_1 = data0_1.reset_index(drop=True)
for index, row in data0_1.iterrows():
    exec("dict['mark'] = '{}'".format(row['mark']))
    new_row = pd.Series(dict)
    coef_df = coef_df.append(new_row, ignore_index=True)
data0_1 = data0[(data0['BIG_VAR']=='烧结煤')&(data0['VAR']=='兰炭')]

data0_1 = data0_1.reset_index(drop=True)
for index, row in data0_1.iterrows():
    exec("dict['mark'] = '{}'".format(row['mark']))
    new_row = pd.Series(dict)
    coef_df = coef_df.append(new_row, ignore_index=True)
mark_list = coef_df['mark'].to_list()
#len1不包括兰炭的煤种个数
data0_1 = data0[(data0['VAR']!='兰炭类')&(data0['VAR']!='兰炭')]
data0_1 = data0_1.reset_index(drop=True)
len1=len(data0_1)
# for mark_tmp in mark_list:
#     exec('c_{} =price_{}'.format(mark_tmp,mark_tmp))
# data0_1 = data0[(data0['BIG_VAR']=='喷吹煤')&(data0['VAR']!='兰炭')]
# data0_1 = data0_1.reset_index(drop=True)
# len1=len(data0_1)
# for index, row in data0_1.iterrows():
#     exec('c1{} =price_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='发电煤')&(data0['VAR']!='兰炭类')]
# data0_1 = data0_1.reset_index(drop=True)
# len2=len(data0_1)
# for index, row in data0_1.iterrows():
#     exec('c2{} =price_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='烧结煤')&(data0['VAR']=='无烟煤')]
# data0_1 = data0_1.reset_index(drop=True)
# len3=len(data0_1)
#
# for index, row in data0_1.iterrows():
#     exec('c3{} =price_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='喷吹煤')&(data0['VAR']=='兰炭')]
# data0_1 = data0_1.reset_index(drop=True)
# len4=len(data0_1)
#
# for index, row in data0_1.iterrows():
#     exec('c4{} =price_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='发电煤')&(data0['VAR']=='兰炭类')]
# data0_1 = data0_1.reset_index(drop=True)
# len5=len(data0_1)
#
# for index, row in data0_1.iterrows():
#     exec('c5{} =price_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='烧结煤')&(data0['VAR']=='兰炭')]
# data0_1 = data0_1.reset_index(drop=True)
# len6=len(data0_1)
#
# for index, row in data0_1.iterrows():
#     exec('c6{} =price_{}'.format(index+1,row['mark']))
a11 = (100 - penchuimeishuifen) / 100 / (100 + meitanwushunlv) * 100
a12 = (100 - lantanshuifen) / 100
a21 = 100 / (100 + meitanwushunlv)
a22 = 100 / (100 + meitanwushunlv)
a31 = (100 - shaojiemeishuifen) / 100 / (100 + meitanwushunlv) * 100
a32 = (100 - shaojiejiaofenshuifen) / 100
b0 = kongmeizhibiao - WT_lianjiaomei
b1 = tieshuichanliang * chunmeibi / 1000
b2 = fadianliang * meidianbi / 100 * fadianmeihao / 100 * (100 + meitanwushunlv) / 100
b3 = shaojiechanliang * shaojieranliaobi / 1000 - jiaotanchanliang * cujiaolv / 100 - WT_waigoujiaotan * waigoujiaofenlv / 100 * (
        100 - waigoujiaoshuifen) / 100
# data0_1 = data0[(data0['BIG_VAR']=='喷吹煤')&(data0['VAR']!='兰炭')]
# data0_1 = data0_1.reset_index(drop=True)
# for index, row in data0_1.iterrows():
#     exec('b01{} =kegongziyuan_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='发电煤')&(data0['VAR']!='兰炭类')]
# data0_1 = data0_1.reset_index(drop=True)
# for index, row in data0_1.iterrows():
#     exec('b02{} =kegongziyuan_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='烧结煤')&(data0['VAR']=='无烟煤')]
# data0_1 = data0_1.reset_index(drop=True)
# for index, row in data0_1.iterrows():
#     exec('b03{} =kegongziyuan_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='喷吹煤')&(data0['VAR']=='兰炭')]
# data0_1 = data0_1.reset_index(drop=True)
# for index, row in data0_1.iterrows():
#     exec('b04{} =kegongziyuan_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='发电煤')&(data0['VAR']=='兰炭类')]
# data0_1 = data0_1.reset_index(drop=True)
# for index, row in data0_1.iterrows():
#     exec('b05{} =kegongziyuan_{}'.format(index+1,row['mark']))
# data0_1 = data0[(data0['BIG_VAR']=='烧结煤')&(data0['VAR']=='兰炭')]
# data0_1 = data0_1.reset_index(drop=True)
# for index, row in data0_1.iterrows():
#     exec('b06{} =kegongziyuan_{}'.format(index+1,row['mark']))
#构建所有参数的df
canshu_df = pd.DataFrame(columns=['编号','参数名','步骤','mark','group','big_var','meizhi'])
dict_canshu = {}
j_start = 0
#必要条件，控煤指标
# array_len = len1+len2+len3+len4+len5+len6
array_len = len(mark_list)
y0 = array([[0]*array_len],dtype=float)
for i in range(0,int(len1)):
    y0[0,i] = 1
m0 = array([b0])
dict_canshu['编号'] = 0
dict_canshu['参数名'] = '控煤指标'
dict_canshu['步骤'] = '前提条件'
new_row = pd.Series(dict_canshu)
canshu_df = canshu_df.append(new_row, ignore_index=True)
dict_canshu = {}
j_start=j_start+1
#可供资源
for j in range(j_start,j_start+int(array_len)):
    exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
    exec('y{}[0,j-j_start] = 1'.format(j))
    mark_tmp = mark_list[j-1]
    exec('m{} = array([kegongziyuan_{}])'.format(j,mark_tmp))
    dict_canshu['编号'] = j
    dict_canshu['参数名'] = '可供资源'
    if mark_tmp[0]=='1':
        buzhou_tmp='喷吹煤'
    elif mark_tmp[0]=='2':
        buzhou_tmp='发电煤'
    elif mark_tmp[0]=='3':
        buzhou_tmp='烧结煤'
    dict_canshu['步骤'] = buzhou_tmp
    dict_canshu['mark'] = mark_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
j_start = j_start+int(array_len)
#品种比例
for mark_tmp in mark_list:
    #上限
    coef_df1 = coef_df.copy()
    mark_tmp_top = mark_tmp[0]
    exec('true_value = 100 - ul_{}'.format(mark_tmp))
    exec('false_value = - ul_{}'.format(mark_tmp))
    def __cal_coef(x):
        if x.mark == mark_tmp:
            rst = true_value
        elif x.mark != mark_tmp and x.mark[0] == mark_tmp_top:
            rst = false_value
        else:
            rst = 0
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '品种比例上限'
    if mark_tmp[0]=='1':
        buzhou_tmp='喷吹煤'
    elif mark_tmp[0]=='2':
        buzhou_tmp='发电煤'
    elif mark_tmp[0]=='3':
        buzhou_tmp='烧结煤'
    dict_canshu['步骤'] = buzhou_tmp
    dict_canshu['mark'] = mark_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start+1
    #下限
    coef_df1 = coef_df.copy()
    mark_tmp_top = mark_tmp[0]
    exec('true_value = ll_{} - 100'.format(mark_tmp))
    exec('false_value = ll_{}'.format(mark_tmp))
    def __cal_coef(x):
        if x.mark == mark_tmp:
            rst = true_value
        elif x.mark != mark_tmp and x.mark[0] == mark_tmp_top:
            rst = false_value
        else:
            rst = 0
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '品种比例下限'
    if mark_tmp[0]=='1':
        buzhou_tmp='喷吹煤'
    elif mark_tmp[0]=='2':
        buzhou_tmp='发电煤'
    elif mark_tmp[0]=='3':
        buzhou_tmp='烧结煤'
    dict_canshu['步骤'] = buzhou_tmp
    dict_canshu['mark'] = mark_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
#性状比例
var_list = var_df['group'].to_list()
for var_tmp in var_list:
    #上限
    coef_df1 = coef_df.copy()
    exec('true_value = 100 - ul_{}'.format(var_tmp))
    exec('false_value = - ul_{}'.format(var_tmp))
    var_tmp_top = var_tmp[0]
    def __cal_coef(x):
        if x.mark[0:3] == var_tmp:
            rst = true_value
        elif x.mark[0:3] != var_tmp and x.mark[0] == var_tmp_top:
            rst = false_value
        else:
            rst = 0
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '性状比例上限'
    if var_tmp[0]=='1':
        buzhou_tmp='喷吹煤'
    elif var_tmp[0]=='2':
        buzhou_tmp='发电煤'
    elif var_tmp[0]=='3':
        buzhou_tmp='烧结煤'
    dict_canshu['步骤'] = buzhou_tmp
    dict_canshu['group'] = var_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}

    j_start = j_start + 1
    #下限
    coef_df1 = coef_df.copy()
    exec('true_value = ll_{} - 100'.format(var_tmp))
    exec('false_value = ll_{}'.format(var_tmp))
    var_tmp_top = var_tmp[0]
    def __cal_coef(x):
        if x.mark[0:3] == var_tmp:
            rst = true_value
        elif x.mark[0:3] != var_tmp and x.mark[0] == var_tmp_top:
            rst = false_value
        else:
            rst = 0
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        exec("y{}[0, index] = row['coef']".format(j_start))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '性状比例下限'
    if var_tmp[0]=='1':
        buzhou_tmp='喷吹煤'
    elif var_tmp[0]=='2':
        buzhou_tmp='发电煤'
    elif var_tmp[0]=='3':
        buzhou_tmp='烧结煤'
    dict_canshu['步骤'] = buzhou_tmp
    dict_canshu['group'] = var_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
#煤质上下限
meizhi_list = ['shui','hui','huifa','liu','gu','mo','re','cc']
meizhi_list_fadian = ['shui','hui','huifa','liu','gu','re','cc']
#喷吹煤
big_var_tmp = 'penchui'
for meizhi_tmp in meizhi_list:
    coef_df1 = coef_df.copy()
    #上限
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        mark_tmp = row['mark']
        mark_tmp_top = mark_tmp[0]
        if mark_tmp_top=='1':
            exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start,meizhi_tmp,mark_tmp,big_var_tmp,meizhi_tmp))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '煤质上限'
    dict_canshu['步骤'] = '煤质上下限'
    dict_canshu['big_var'] = '喷吹煤'
    dict_canshu['meizhi'] = meizhi_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
    #下限
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        mark_tmp = row['mark']
        mark_tmp_top = mark_tmp[0]
        if mark_tmp_top=='1':
            exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start,big_var_tmp,meizhi_tmp,meizhi_tmp,mark_tmp))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '煤质下限'
    dict_canshu['步骤'] = '煤质上下限'
    dict_canshu['big_var'] = '喷吹煤'
    dict_canshu['meizhi'] = meizhi_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
#发电煤
big_var_tmp = 'fadian'
for meizhi_tmp in meizhi_list_fadian:
    coef_df1 = coef_df.copy()
    #上限
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        mark_tmp = row['mark']
        mark_tmp_top = mark_tmp[0]
        if mark_tmp_top=='2':
            exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start,meizhi_tmp,mark_tmp,big_var_tmp,meizhi_tmp))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '煤质上限'
    dict_canshu['步骤'] = '煤质上下限'
    dict_canshu['big_var'] = '发电煤'
    dict_canshu['meizhi'] = meizhi_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
    #下限
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        mark_tmp = row['mark']
        mark_tmp_top = mark_tmp[0]
        if mark_tmp_top=='2':
            exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start,big_var_tmp,meizhi_tmp,meizhi_tmp,mark_tmp))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '煤质下限'
    dict_canshu['步骤'] = '煤质上下限'
    dict_canshu['big_var'] = '发电煤'
    dict_canshu['meizhi'] = meizhi_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
#烧结煤
big_var_tmp = 'shaojie'
for meizhi_tmp in meizhi_list:
    coef_df1 = coef_df.copy()
    #上限
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        mark_tmp = row['mark']
        mark_tmp_top = mark_tmp[0]
        if mark_tmp_top=='3':
            exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start,meizhi_tmp,mark_tmp,big_var_tmp,meizhi_tmp))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '煤质上限'
    dict_canshu['步骤'] = '煤质上下限'
    dict_canshu['big_var'] = '烧结煤'
    dict_canshu['meizhi'] = meizhi_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
    #下限
    exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
    for index, row in coef_df1.iterrows():
        mark_tmp = row['mark']
        mark_tmp_top = mark_tmp[0]
        if mark_tmp_top=='3':
            exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start,big_var_tmp,meizhi_tmp,meizhi_tmp,mark_tmp))
    exec("m{} =  array([0])".format(j_start))
    dict_canshu['编号'] = j_start
    dict_canshu['参数名'] = '煤质下限'
    dict_canshu['步骤'] = '煤质上下限'
    dict_canshu['big_var'] = '烧结煤'
    dict_canshu['meizhi'] = meizhi_tmp
    new_row = pd.Series(dict_canshu)
    canshu_df = canshu_df.append(new_row, ignore_index=True)
    dict_canshu = {}
    j_start = j_start + 1
print('y生成完毕')
#拼接
y_str = 'y0'
for j in range(1,j_start):
    y_str = y_str +',' + 'y'+str(j)
exec('yyy =({})'.format(y_str))
Y = np.concatenate(yyy, axis=0)

m_str = 'm0'
for j in range(1,j_start):
    m_str = m_str +',' + 'm'+str(j)
exec('mmm =({})'.format(m_str))
M = np.concatenate(mmm, axis=0)
print('finish')
c_str = ''
for mark_tmp in mark_list:
    if mark_tmp == '1_1_1':
        c_str = c_str + 'price_1_1_1'
    else:
        exec("c_str = c_str + ',' + 'price_{}'".format(mark_tmp))
exec("c = array([{}])".format(c_str))
c = array([[1] * array_len], dtype=float)

def judge_solution_exist(e1, f1):
    """
    传入e1，f1进行线性规划求解，判断是否有可行域
    """
    coef_df1 = coef_df.copy()
    def __cal_coef(x):
        if x.mark[0:3] != '1_3' and x.mark[0]=='1':
            rst = a31
        elif x.mark[0:3] == '1_3':
            rst = a32
        else:
            rst = 0
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    e21 = array([[0] * array_len], dtype=float)
    for index, row in coef_df1.iterrows():
        e21[0, index] = row['coef']
    coef_df1 = coef_df.copy()
    def __cal_coef(x):
        if x.mark[0:3] != '2_3' and x.mark[0]=='2':
            rst = a31
        elif x.mark[0:3] == '2_3':
            rst = a32
        else:
            rst = 0
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    e22 = array([[0] * array_len], dtype=float)
    for index, row in coef_df1.iterrows():
        e22[0, index] = row['coef']
    coef_df1 = coef_df.copy()
    def __cal_coef(x):
        if x.mark[0:3] != '3_2' and x.mark[0]=='3':
            rst = a31
        elif x.mark[0:3] == '3_2':
            rst = a32
        else:
            rst = 0
        return rst
    coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
    e23 = array([[0] * array_len], dtype=float)
    for index, row in coef_df1.iterrows():
        e23[0, index] = row['coef']
    e222 = (e21, e22, e23)
    e2 = np.concatenate(e222, axis=0)
    f2 = array([b1, b2, b3])
    x = cp.Variable(array_len)
    obj = cp.Minimize(c @ x)
    cons = [e1 @ x <= f1, e2 @ x == f2, x >= 0]
    prob = cp.Problem(obj, cons)
    prob.solve(solver='GLPK_MI', verbose=True)
    print("最优初始值为:", prob.value)
    print("最优初始解为：\n", x.value)
    success = 0
    if x.value is None:
        success = 0
    else:
        success = 1
    print(success)
    return success

success = judge_solution_exist(e1=Y, f1=M)
yy = y0
mm = m0
listb = []
if success == 1:
    message = '约束条件合理，存在最优解'
    listd = ['约束条件合理，存在最优解']
    code = '1'
    print(message)
else:
    message = '约束条件不合理，不存在最优解'
    print(message)
    print('无解，开始循环找需要修改的约束条件')
    code = '0'
    for i in range(1, j_start):
        yi = Y[[i]]
        mi = M[[i]]
        Yy = np.concatenate((yy, yi), axis=0)
        Mm = np.concatenate((mm, mi), axis=0)
        success = judge_solution_exist(e1=Yy, f1=Mm)
        if success == 1:
            yy = Yy
            mm = Mm
        else:
            yy = yy
            mm = mm
            # print(i)
            listb.append(i)
            # print(listb)
    df2 = canshu_df.iloc[listb]
    print('需要修改的约束条件参数为')
    print(df2)


print('finish')



